﻿using DataGridViewAutoFilter;
using NPOI.POIFS.Crypt.Dsig;
using Sunny.UI;
using Sunny.UI.Win32;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExcelToData
{
    public partial class FormMain : UIForm
    {
        //声明默认配置文件路径
        public string INIPath = Convert.ToString(System.AppDomain.CurrentDomain.BaseDirectory) + "Config.ini";
        EncryptionDecryption encryptionDecryption = new EncryptionDecryption();
        DataSet ds = new DataSet();
        delegate void regriddelegate();
        string filename = "";

        public FormMain()
        {
            InitializeComponent();
        }

        private void btnOpen_Click(object sender, EventArgs e)
        {
            ds.Clear();
            TabCtr.Controls.Clear();
            oFDialog.Filter = "Excel 工作簿|*.xls;*.xlsx|Excel 工作簿|*.xlsx|Excel 97-2003 工作簿|*.xls|所有文件|*.*";
            if (oFDialog.ShowDialog() == DialogResult.OK)
            {
                string path = oFDialog.FileName;
                txtPath.Text = path;
                                
                try
                {
                    ds = ExcelHelper.ExcelToDataSet(path);
                }catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally { }
                if (cBox.Checked)
                {
                    foreach (DataTable dt in ds.Tables)
                    {
                        Table(dt, dt.TableName);
                    }
                }
                else
                {
                    UIMessageTip.ShowOk("加载完成");
                }
            }
        }


        private void btnSave_Click(object sender, EventArgs e)
        {

            string tableName = txtTableName.Text;

            if (ds.Tables.Count == 0)
            {
                UIMessageTip.ShowWarning("没有加载任何表");
                txtPath.Focus();
                return;
            }

            if (string.IsNullOrEmpty(tableName))
            {
                UIMessageTip.ShowWarning("请输入表名前缀");
                txtTableName.Focus();
                return;
            }            
           
            foreach (DataTable dts in ds.Tables)
            {
                string result = "";
                if (cBox.Checked)
                {
                    Control[] controlArray = this.Controls.Find(dts.TableName, true);
                    if (controlArray != null && controlArray.Length > 0)
                    {
                        Sunny.UI.UIDataGridView dGView = (Sunny.UI.UIDataGridView)controlArray[0];
                        DataTable dt = dGView.DataSource as DataTable;
                        result = MsSqlHelper.TableByData(dt, tableName+"_"+dts.TableName);                        
                    }
                }
                else
                {
                    result = MsSqlHelper.TableByData(dts, tableName + "_" + dts.TableName);
                }
                UIMessageTip.ShowOk(dts.TableName + " 执行耗时：" + result + "毫秒");
            }
            
        }

        private void FormMain_Load(object sender, EventArgs e)
        {
            CbBDataType.Items.AddRange(new object[] {"SqlServer", "Oracle", "MySql"});
            filename = Application.StartupPath + "\\Config.ini";
            AppCode.ClearLog();
        }

        private void TabCtr_SelectedIndexChanged(object sender, EventArgs e)
        {
            //UIMessageTip.ShowOk(TabCtr.SelectedIndex.ToString());
            if (TabCtr.SelectedIndex !=-1)
            {
                string TabName = TabCtr.SelectedTab.Name;
                UIMessageTip.ShowOk(TabName.ToString());
                Control[] controlArray = this.Controls.Find("page_" + TabName, true);
                if (controlArray != null && controlArray.Length > 0)
                {                    
                    Sunny.UI.UIPagination pagination = (Sunny.UI.UIPagination)controlArray[0];
                    pagination.ActivePage = 1;
                }
            }
        }

        private void Table(DataTable dt, String PageName)
        {
            System.Windows.Forms.TabPage tabTage = new System.Windows.Forms.TabPage();
            tabTage.Text = PageName;
            tabTage.Name = PageName;

            TabCtr.Controls.Add(tabTage);

            Sunny.UI.UIDataGridView dGView = new Sunny.UI.UIDataGridView();
            Sunny.UI.UIPagination pagination = new Sunny.UI.UIPagination();
            pagination.Dock = System.Windows.Forms.DockStyle.Bottom;
            pagination.Size = new System.Drawing.Size(600, 35);
            pagination.PageSize = 10;
            pagination.TotalCount = dt.Rows.Count;
            pagination.CausesValidation = false;            
            pagination.Name = "page_" + PageName;            
            pagination.PageChanged += new Sunny.UI.UIPagination.OnPageChangeEventHandler(pagination_PageChanged);
            dGView.RowHeadersWidthSizeMode = System.Windows.Forms.DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders;            
            dGView.Dock = System.Windows.Forms.DockStyle.Fill;
            int height=dGView.Height;
            dGView.AllowUserToAddRows = false;
            dGView.AutoResizeColumns();
            dGView.ColumnHeadersBorderStyle = System.Windows.Forms.DataGridViewHeaderBorderStyle.Raised;
            dGView.Location = new System.Drawing.Point(0, 0);
            dGView.RowStateChanged += new System.Windows.Forms.DataGridViewRowStateChangedEventHandler(dGView_RowStateChanged);
            dGView.KeyDown += new System.Windows.Forms.KeyEventHandler(dGView_KeyDown);

            dGView.SelectIndexChange += dGView_SelectIndexChange;
            dGView.Name ="dgv_"+ PageName;    
            tabTage.Controls.Add(dGView);
            tabTage.Controls.Add(pagination);
            pagination.ActivePage = 1;
        }

        private void dGView_SelectIndexChange(object sender, int index)
        {
            index.WriteConsole("SelectedIndex");
        }

        private void dGView_RowStateChanged(object sender, DataGridViewRowStateChangedEventArgs e)
        {
            e.Row.HeaderCell.Value = string.Format("{0}", e.Row.Index + 1);
        }

        private void cBox_CheckedChanged(object sender, EventArgs e)
        {
            TabCtr.Controls.Clear();
            if (cBox.Checked)
            {                
                foreach (DataTable dt in ds.Tables)
                {
                    Table(dt, dt.TableName);
                }
            }
        }

        /// <summary>
        /// 分页控件页面切换事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="pagingSource"></param>
        /// <param name="pageIndex"></param>
        /// <param name="count"></param>
        private void pagination_PageChanged(object sender, object pagingSource, int pageIndex, int count)
        {

            string TabName=TabCtr.SelectedTab.Name;
           
            DataTable dtview = ds.Tables[TabName];
            DataTable data = dtview.Clone();
            for (int i = (pageIndex - 1) * count; i < (pageIndex - 1) * count + count; i++)
            {
                if (i >= dtview.Rows.Count) continue;
                data.Rows.Add(dtview.Rows[i].ItemArray);
            }
            
            Control[] controlArray = this.Controls.Find("dgv_" + TabName, true);
            if (controlArray != null && controlArray.Length > 0)
            {
                Sunny.UI.UIDataGridView dGView = (Sunny.UI.UIDataGridView)controlArray[0];
                BindingSource dataSource = new BindingSource(data, null);
                dGView.DataSource = dataSource;
                //在表头增加自动过滤功能
                foreach (DataGridViewColumn col in dGView.Columns)
                {
                    col.HeaderCell = new DataGridViewAutoFilterColumnHeaderCell(col.HeaderCell);
                }
            }
        }

        private void dGView_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Alt && (e.KeyCode == Keys.Down || e.KeyCode == Keys.Up))
            {
                if (TabCtr.SelectedIndex != -1)
                {
                    string TabName = TabCtr.SelectedTab.Name;
                    UIMessageTip.ShowOk(TabName.ToString());
                    Control[] controlArray = this.Controls.Find("dgv_" + TabName, true);
                    if (controlArray != null && controlArray.Length > 0)
                    {
                        Sunny.UI.UIDataGridView dGView = (Sunny.UI.UIDataGridView)controlArray[0];
                        DataGridViewAutoFilterColumnHeaderCell filterCell =
                    dGView.CurrentCell.OwningColumn.HeaderCell as
                    DataGridViewAutoFilterColumnHeaderCell;
                        if (filterCell != null)
                        {
                            filterCell.ShowDropDownList();
                            e.Handled = true;
                        }
                    }
                }                
            }
        }

        private void btnconnet_Click(object sender, EventArgs e)
        {
            if (btnconnet.Text == "断  开") {
                btnconnet.Text = "链  接";
                btnSave.Enabled = false;
                CbBDataType.Enabled = true;
                txtServer.Enabled = true;
                txtPort.Enabled = true;
                txtUserName.Enabled = true;
                txtPassword.Enabled = true;
                txtDataName.Enabled = true;
                UIMessageTip.ShowWarning("链接已断开");
            }
            else
            {

                bool Flag = false;
                int sqltype = CbBDataType.SelectedIndex;
                string server = txtServer.Text;
                int port = int.Parse(txtPort.Text);
                string username = txtUserName.Text;
                string password = txtPassword.Text;
                string dataname = txtDataName.Text;
                if (sqltype<0)
                {
                    UIMessageTip.ShowWarning("数据库类型没有选择");
                    CbBDataType.Focus();
                    return;
                }
                if (string.IsNullOrEmpty(server))
                {
                    UIMessageTip.ShowWarning("服务器地址没有填写");
                    txtServer.Focus();
                    return;
                }
                if (port==0)
                {
                    UIMessageTip.ShowWarning("端口号没有填写");
                    txtPort.Focus();
                    return;
                }
                if (string.IsNullOrEmpty(username))
                {
                    UIMessageTip.ShowWarning("登录账号没有填写");
                    txtUserName.Focus();
                    return;
                }
                if (string.IsNullOrEmpty(password))
                {
                    UIMessageTip.ShowWarning("登录密码没有填写");
                    txtPassword.Focus();
                    return;
                }
                if (string.IsNullOrEmpty(dataname))
                {
                    UIMessageTip.ShowWarning("数据库名称没有填写");
                    txtDataName.Focus();
                    return;
                }
                Flag = AppCode.TestConnection(server, port);
                if (Flag)
                {
                    if (sqltype == 0)
                    {
                        AppCode.connectionString = String.Format("Data Source ={0},{1}; Initial Catalog = {2}; User ID = {3}; Password={4};Connection Timeout=3", server, port, dataname, username, password);
                        MsSqlHelper msSqlHelper = new MsSqlHelper();
                        if (!MsSqlHelper.IsConNet())                        
                        {
                            Flag = false;
                            UIMessageTip.ShowWarning("账号、密码错误或数据库不正确");
                        }
                        else
                        {
                            AppCode.UpdateConnectionStringsConfig("SqlServer", AppCode.connectionString, "System.Data.Sqlclient");
                        }
                    }
                    if (sqltype == 1)
                    {
                        AppCode.connectionString = String.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})) (CONNECT_DATA=(SERVICE_NAME={2})));User ID={3};Password={4};", server, port, dataname, username, password);
                        OracleHelper oracleHelper = new OracleHelper();
                        if (!OracleHelper.IsConNet())
                        {
                            Flag = false;
                            UIMessageTip.ShowWarning("账号、密码错误或数据库不正确");
                        }
                        else
                        {
                            AppCode.UpdateConnectionStringsConfig("Oracle", AppCode.connectionString, "Oracle.ManagedDataAccess.Client");
                        }
                    }
                    if (sqltype == 2)
                    {
                        AppCode.connectionString = String.Format("Data Source = {0}; Port = {1}; DataBase = {2}; User = {3}; Password = {4}; Charset = utf8;", server, port, dataname, username, password);
                        MySqlHelper mySqlHelper = new MySqlHelper();
                        if (!MySqlHelper.IsConNet())
                        {
                            Flag = false;
                            UIMessageTip.ShowWarning("账号、密码错误或数据库不正确");
                        }
                        else
                        {
                            AppCode.UpdateConnectionStringsConfig("MySql", AppCode.connectionString, "MySqlConnector");
                        }
                    }
                    if (Flag)
                    {
                        btnconnet.Text = "断  开";
                        btnSave.Enabled = true;
                        CbBDataType.Enabled = false;
                        txtServer.Enabled = false;
                        txtPort.Enabled = false;
                        txtUserName.Enabled = false;
                        txtPassword.Enabled = false;
                        txtDataName.Enabled = false;
                        UIMessageTip.ShowOk("数据库链接成功");
                        WriteConnet();
                    }
                }
                else
                {
                    UIMessageTip.ShowError("服务器不存在或不可用");
                }
            }
        }        

        private void CbBDataType_SelectedIndexChanged(object sender, EventArgs e)
        {
            int sqltype=CbBDataType.SelectedIndex;
            string typeStr = "";
            switch (sqltype)
            {
                case 0:
                    txtPort.Text = "1433";
                    typeStr = "SQL SERVER";
                    break;
                    case 1:
                    typeStr = "Oracle";
                    txtPort.Text = "1521";
                    break ;
                    case 2:
                    typeStr = "MYSQL";
                    txtPort.Text = "3306";
                    break;
                default:
                    txtPort.Text = "0";
                    break;
            }
            ReadConnet(typeStr);
        }

        #region INI文件读写
        private void ReadConnet(string typeStr)
        {
            try
            {
                string server = IniFunc.getString(typeStr, " 连接地址", null, filename);
                string port = IniFunc.getString(typeStr, "端口号", null, filename);
                string username = IniFunc.getString(typeStr, "登录账号", null, filename);
                string password = IniFunc.getString(typeStr, "登录密码", null, filename);
                string dataname = IniFunc.getString(typeStr, "数据库名", null, filename);
                txtServer.Text = server;                
                txtUserName.Text = username;
                txtPassword.Text = encryptionDecryption.decode(password);
                txtDataName.Text = dataname;
                txtPort.Text = int.Parse(port).ToString();
            }
            catch
            {
                IniFunc.writeString(typeStr, "连接地址", null, filename);
                IniFunc.writeString(typeStr, "端口号", null, filename);
                IniFunc.writeString(typeStr, "登录账号", null, filename);
                IniFunc.writeString(typeStr, "登录密码", null, filename);
                IniFunc.writeString(typeStr, "数据库名", null, filename);
            }
        }
        private void WriteConnet()
        {
            try
            {
                int sqltype = CbBDataType.SelectedIndex;
                string typeStr = "";
                switch (sqltype)
                {
                    case 0:
                        txtPort.Text = "1433";
                        typeStr = "SQL SERVER";
                        break;
                    case 1:
                        typeStr = "Oracle";
                        txtPort.Text = "1521";
                        break;
                    case 2:
                        typeStr = "MYSQL";
                        txtPort.Text = "3306";
                        break;
                    default:
                        txtPort.Text = "0";
                        break;
                }    

                string server = txtServer.Text;
                string port = txtPort.Text;
                string username = txtUserName.Text;
                string password = encryptionDecryption.encode(txtPassword.Text);
                string dataname = txtDataName.Text;
                IniFunc.writeString(typeStr, "连接地址", server, filename);
                IniFunc.writeString(typeStr, "端口号", port, filename);
                IniFunc.writeString(typeStr, "登录账号", username, filename);
                IniFunc.writeString(typeStr, "登录密码", password, filename);
                IniFunc.writeString(typeStr, "数据库名", dataname, filename);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion
    }
}
